﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlDepartement
    {
        private static Departement ReaderToObject(SqlDataReader dr)
        {
            Departement _departement = new Departement();
            _departement.Id = int.Parse(dr["Id"].ToString());

            _departement.Nom = dr["Nom"].ToString();
            return _departement;
    
        }

        public static Departement GetDepartementById(int id){
            Departement departement = new Departement();
            string strSQL = @"select * from Departement where UserId=@Id";
            try{
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read()){
                        departement = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();

                }
            }
            catch (Exception) { }
            return departement;
        }
         
        public static int insertdepartement(Departement d)
        {
            int x = 0;            
            string strSQL = @"insert into Departement(Nom) values(@nom)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString)){
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                  
                    cmd.Parameters.AddWithValue("@nom", d.Nom);
                   
                    x = cmd.ExecuteNonQuery();
                    cnx.Close();                    
                }
            }
            catch (Exception) { }
            return x;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateDepartement( Departement d)
        {
             int x = 0;

             string strSQl = @"UPDATE Departement SET (@nom IS NULL OR Nom=@nom) where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    
                     SqlCommand cmd = new SqlCommand(strSQl, cnx);
                    
                    cmd.Parameters.AddWithValue("@nom", d.Nom);

                    x = cmd.ExecuteNonQuery();
                    
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return x;
        }
       


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletDepartement(int id){ 
            int x = 0;            
            string strSQL = @"delete from Departement where id=@id)";
           
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    
                    cmd.Parameters.AddWithValue("@id", id);

                   x= cmd.ExecuteNonQuery();
                 
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return x;
        }

    }
    }
   
